﻿--use PolePosition
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_VendorDistributionPole_SelectForGrid')
	DROP PROCEDURE up_VendorDistributionPole_SelectForGrid
GO

CREATE PROCEDURE up_VendorDistributionPole_SelectForGrid
	 @Vendor_ProjectFK int 
	,@PageSize int = 100
	,@PageNumber int = 1
	,@SearchByType varchar(50) = null
	,@SearchByText varchar(50) = null
	
AS

SET NOCOUNT ON	

DECLARE @StartRowNumber int
DECLARE @EndRowNumber int

SET @StartRowNumber = ((@PageNumber - 1) * @PageSize) + 1
SET @EndRowNumber = @PageNumber * @PageSize


DECLARE @table table
(
 	 RowNumber int
	,TotalCount int
	,Vendor_Distribution_Poles_PoleId int
	,MapFk int
	,SectionFk int
	,SpecieFk int
	,ClassFK int
	,LengthFk int
	,TreatmentFk int
	,Latitude float
	,Longitude float
	,InspBy nvarchar(100)
	,Number nvarchar(100)
	,AcquisitionDate datetime
	,Year int
	,LVisit datetime
	,D804 nvarchar(50)
	,DistributionType int
	,UserFk int
	,VendorFk int
	,InspectorFk int
	,ProjectFk int
	,[FriendlyName] varchar(255)
	,VendorName varchar(50)
	,VendorContactName varchar(50)		
	
	,Vendor_Distribution_TypeFk int
	,Vendor_Distribution_ResultTypeFk int
	,bElectrical bit
	,Comments ntext
	
	,Utility_Asset_Distributions_PoleFk int
	,GuardsQuantity int
	,PoleTag bit
	,GroundWireRepair bit
	,Billable bit
	
	
	,TopTypeDisplay varchar(MAX)
	,NoteDisplay varchar(MAX)
	,PhaseDisplay varchar(MAX)
	,Kv	int
	,Circuit	varchar(MAX)
	,ConductorType int
	,ACSR	varchar(MAX)
)
	
INSERT @table
(
	 [RowNumber]
	,[TotalCount]
	,[Vendor_Distribution_Poles_PoleId]
	,[MapFk]
	,[SectionFk]
	,[SpecieFk]
	,[ClassFK]
	,[LengthFk]
	,[TreatmentFk]
	,[Latitude]
	,[Longitude]
	,[InspBy]
	,[Number]
	,[AcquisitionDate]
	,[Year]
	,[LVisit]
	,[D804]
	,[DistributionType]
	,[UserFk]
	,[VendorFk]
	,[InspectorFk]
	,[ProjectFk]
	,[FriendlyName]
	,[Utility_Asset_Distributions_PoleFk]
	,[GuardsQuantity]
	,[PoleTag]
	,[GroundWireRepair]
	,[Billable]
	,VendorName
	,VendorContactName	
	,Vendor_Distribution_TypeFk
	,Vendor_Distribution_ResultTypeFk
	,bElectrical
	,Comments
	,TopTypeDisplay
	,NoteDisplay
	,PhaseDisplay
	,Kv
	,Circuit
	,ConductorType
	,ACSR
)
SELECT 
*	
FROM 
(
	SELECT 
		 ROW_NUMBER() OVER (ORDER BY Pole.Vendor_Distribution_Poles_PoleId) AS RowNumber
		,COUNT(Pole.Vendor_Distribution_Poles_PoleId) OVER (PARTITION BY NULL) AS TotalCount
		,Pole.[Vendor_Distribution_Poles_PoleId]
		,Pole.[MapFk]
		,Pole.[SectionFk]
		,Pole.[SpecieFk]
		,Pole.[ClassFK]
		,Pole.[LengthFk]
		,Pole.[TreatmentFk]
		,Pole.[Latitude]
		,Pole.[Longitude]
		,Pole.[InspBy]
		,Pole.[Number]
		,Pole.[AcquisitionDate]
		,Pole.[Year]
		,Pole.[LVisit]
		,Pole.[D804]
		,Pole.[DistributionType]
		,Pole.[UserFk]
		,Pole.[VendorFk]
		,Pole.[InspectorFk]
		,Pole.[ProjectFk]
		,Pole.[FriendlyName]
		,Pole.[Utility_Asset_Distributions_PoleFk]
		,Pole.[GuardsQuantity]
		,Pole.[PoleTag]
		,Pole.[GroundWireRepair]
		,Pole.[Billable]		
		,Vendors.Name AS VendorName	
		,Contact.Name AS VendorContactName		
		,Result.Vendor_Distribution_TypeFk
		,Result.Vendor_Distribution_ResultTypeFk
		,Result.bElectrical
		,Result.Comments
		,dbo.fn_DistributionPole_GetTopTypeString(Pole.Vendor_Distribution_Poles_PoleId, 0) AS TopTypeDisplay
		,dbo.fn_VendorDistributionPole_GetNoteString(Pole.Vendor_Distribution_Poles_PoleId) AS NoteDisplay
		,dbo.fn_DistributionPole_GetPhaseString(Pole.Vendor_Distribution_Poles_PoleId, 0) AS PhaseDisplay
		,Pole.Kv
		,Pole.Circuit
		,Pole.ConductorType
		,Pole.ACSR
	FROM v_tb_Vendor_Distribution_Poles Pole
	LEFT JOIN tb_Vendor_Distribution_InspectionResults Result
		ON Result.Vendor_Distribution_PoleFk = Pole.Vendor_Distribution_Poles_PoleId
	LEFT JOIN tb_Vendor_Contacts Contact
		ON Contact.Vendor_ContactId = Pole.InspectorFk
	LEFT JOIN tb_Vendors Vendors 
		ON Vendors.VendorId = Pole.VendorFk
	WHERE 
		Pole.[ProjectFk] = @Vendor_ProjectFK
) AS Data
WHERE RowNumber BETWEEN @StartRowNumber AND @EndRowNumber


-- primary select
select * from @table

	-- VendorDistributionInspectionResultCollection select

select
	v_tb_Vendor_Distribution_InspectionResults.* 
from 
	v_tb_Vendor_Distribution_InspectionResults 
	JOIN @table x ON v_tb_Vendor_Distribution_InspectionResults.Vendor_Distribution_PoleFk = x.Vendor_Distribution_Poles_PoleId
	


	-- VendorDistributionMeterCollection select
select
	v_tb_Vendor_Distribution_Meters.* 
from 
	v_tb_Vendor_Distribution_Meters 
	JOIN @table x ON v_tb_Vendor_Distribution_Meters.Vendor_Distribution_PoleFk = x.Vendor_Distribution_Poles_PoleId
	


select
	v_tb_Vendor_Distribution_Transformers.* 
from 
	v_tb_Vendor_Distribution_Transformers
	JOIN @table x ON v_tb_Vendor_Distribution_Transformers.Vendor_Distribution_PoleFk = x.Vendor_Distribution_Poles_PoleId
	

select
	v_tb_Vendor_Distribution_Sectionalizers.* 
from 
	v_tb_Vendor_Distribution_Sectionalizers
	JOIN @table x ON v_tb_Vendor_Distribution_Sectionalizers.Vendor_Distribution_PoleFk = x.Vendor_Distribution_Poles_PoleId
	
select
	v_tb_Vendor_Distribution_Reclosers.* 
from 
	v_tb_Vendor_Distribution_Reclosers
	JOIN @table x ON v_tb_Vendor_Distribution_Reclosers.Vendor_Distribution_PoleFk = x.Vendor_Distribution_Poles_PoleId


